package cn.e2win.biz.comm;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import cn.e2win.biz.sys.Dict;
/** 
* 生成excel视图，可用excel工具打开或者保存 
* 由ViewController的return new ModelAndView(viewExcel, model)生成 
*/  
public class ViewExcel extends AbstractExcelView {     
	private int createHeader(int rowNum, HSSFWorkbook workbook, HSSFSheet sheet, List<Cell> cellList){

    	// 创建表头样式  
    	HSSFCellStyle headerStyle = workbook.createCellStyle();  
		// 指定单元格居中对齐  
    	headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		// 指定单元格垂直居中对齐  
    	headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
		// 指定当单元格内容显示不下时不自动换行  
    	headerStyle.setWrapText(false);  
		// 设置单元格字体  
		HSSFFont font = workbook.createFont(); 
		//字体加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
		//font.setFontName("宋体");  
		//font.setFontHeight((short) 200);  
		headerStyle.setFont(font); 
		    	

        HSSFRow header = sheet.createRow(rowNum); // 第0行  
    	header.createCell(0).setCellValue("No.");  
    	header.getCell(0).setCellStyle(headerStyle);
        Cell cell = null;
        if(cellList == null || cellList.size()==0){
        	return rowNum;
        }else{
	    	for(int i=0; i<cellList.size();i++){
	        	cell = cellList.get(i);
	        	header.createCell(i+1).setCellValue(cell.getHeader());  
	            header.getCell(i+1).setCellStyle(headerStyle);
	        }
	    	
			return rowNum + 1;
        }
	}
	
	private int createBody(int rowNum, HSSFWorkbook workbook, HSSFSheet sheet, List<Cell> cellList, List<Map<String,Object>> list){

    	HSSFCellStyle doubleStyle = workbook.createCellStyle();
    	Short doubleFormat = workbook.createDataFormat().getFormat("0.0000");
    	doubleStyle.setDataFormat(doubleFormat);
    	HSSFCellStyle intStyle = workbook.createCellStyle();
    	Short intFormat = workbook.createDataFormat().getFormat("0");
    	intStyle.setDataFormat(intFormat);
	  
        // 产生Excel表头    
        String dataType = null;
        Object value = null;
        Cell cell;
        if(list==null || list.size()==0){
        	return rowNum;
        }else{
	        for (int k=0; k<list.size();k++) { 
	        	Map<String, Object> map = list.get(k);
	            HSSFRow row = sheet.createRow(rowNum + k);
	            row.createCell(0).setCellValue(k + 1);
	            for(int i=0; i<cellList.size();i++){
	            	cell = cellList.get(i);
	            	value = map.get(cell.getFiled());
	            	dataType = cell.getDataTyp();
		    		if(value != null){
		    			if(dataType!=null && dataType.indexOf(ExcelTpl.DICT)==0){
		    				value = Dict.getAlias(dataType.replaceAll(ExcelTpl.DICT, ""), value.toString());
			        		row.createCell(i+1).setCellValue(value==null?"":value.toString()); 
			    		}else if(dataType!=null && dataType.indexOf(ExcelTpl.FORMULA)==0){
			    			dataType = dataType.replaceAll(ExcelTpl.FORMULA, "");
			    			value = dataType.replaceAll("【】", ""+rowNum);
			        		row.createCell(i+1).setCellFormula(value==null?"":value.toString()); 
			    		}else if (value != null && "DECIMAL".equals(dataType)){
				    			if(value instanceof BigDecimal){
					        		row.createCell(i+1).setCellValue(((BigDecimal)value).doubleValue()); 	
				    				row.getCell(i+1).setCellStyle(doubleStyle);	    				
				    			}else if(value instanceof Integer){
					        		row.createCell(i+1).setCellValue(((Integer)value).doubleValue()); 	
				    				row.getCell(i+1).setCellStyle(intStyle);	    
				    			}else{
				    				row.createCell(i+1).setCellValue(value.toString());
				    			}
			    		}else{
		    				row.createCell(i+1).setCellValue(value.toString());
		    			}
	    			}
	            }
	        }
	        return rowNum + list.size();
        }
	}

	private int createCountRow(int rowNum, HSSFWorkbook workbook, HSSFSheet sheet, Map<Integer,Cell> countRow){


        if(countRow == null || countRow.size() ==0){
        	return rowNum;
        }else{
        	HSSFCellStyle doubleStyle = workbook.createCellStyle();
        	Short doubleFormat = workbook.createDataFormat().getFormat("0.00");
        	doubleStyle.setDataFormat(doubleFormat);
            
            HSSFRow row = sheet.createRow(rowNum);
            Cell cell;
	        for(Integer i :countRow.keySet()){
	        	cell = countRow.get(i);
		        String value = cell.getFiled();
		        if(value != null){
		        	if(value.indexOf(ExcelTpl.DICT)==0){
		    			value = (String)Dict.getAlias(value.replaceAll(ExcelTpl.DICT, ""), value);
		        		row.createCell(i).setCellValue(value==null?"":value.toString()); 
		    		}else if(value.indexOf(ExcelTpl.FORMULA)==0){
		    			value = value.replaceAll(ExcelTpl.FORMULA, "");
		    			value = value.replaceAll("【】", ""+rowNum);
		        		row.createCell(i).setCellFormula(value); 
		    		}else{
		    			row.createCell(i).setCellValue(value);
		    		}
		        	row.getCell(i).setCellStyle(doubleStyle);
		        }
	        }
	        return rowNum + 1;
		}
	}
     
    public void buildExcelDocument(Map model, HSSFWorkbook workbook,     
            HttpServletRequest request, HttpServletResponse response)     
            throws Exception {
        ExcelTpl tpl = ExcelTpl.getTpl(model.get("key"));
        String excelName = tpl.getTitle();  
        // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开  
        response.setContentType("APPLICATION/OCTET-STREAM");  
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename="+ new String(excelName.getBytes("gb2312"),"ISO8859-1") + ".xls");    
                
        // 产生Excel表头  
        HSSFSheet sheet = workbook.createSheet();  
        int rowNum = createHeader(0, workbook,sheet,tpl.getCellList());
        rowNum = createBody(rowNum,workbook,sheet,tpl.getCellList(),(List<Map<String, Object>>)model.get("list"));
        rowNum = createCountRow(rowNum,workbook,sheet,tpl.getCountRow());
        
        
        
  
    }     
}  